/*
	This proc gives the close lots information and there corresponding TEST yield.
	EXEC SP_TEST_BIN_YIELD_REPORT '0','0','0','0','01/01/2011','05/30/2011','XLREPORT', '0'
	EXEC SP_TEST_BIN_YIELD_REPORT '0','0','0','0','10/01/2010','10/31/2010','SUMMARYREPORT', '0'
	EXEC SP_TEST_BIN_YIELD_REPORT '0','0','0','0','10/01/2010','10/31/2010','DETAILREPORT', '0'
*/
ALTER PROCEDURE sp_test_bin_yield_report
(
	@PROCESS_TYPE		VARCHAR(50),
	@PROCESSSTEP_IDS	VARCHAR(100),
	@BASEPRODUCT_ID		VARCHAR(50),
	@VENDOR			    VARCHAR(100),
	@FROMDATE		    VARCHAR(50),
	@TODATE 		    VARCHAR(50),
	@REPORT_TYPE		VARCHAR(50),
	@DEFAULT_PROMPT		VARCHAR(50)
)

 AS
 BEGIN
				
	CREATE TABLE #RESULT_TABLE ( [BASE PRODUCT] VARCHAR(50), [PART NUMBER] VARCHAR(50), [PROCESSSTEP ID] BIGINT,[LOT NUMBER] VARCHAR(50),[FAB LOT NUMBER] VARCHAR(50),
						[Test In Date] DATETIME, [Test Out Date] DATETIME, [In Qty] INT, [Out Qty] INT,[Yield] FLOAT,[Vendor]	VARCHAR(50),[PROCESS TYPE] VARCHAR(20),
						[Bin 1] INT,[Bin 2] INT,[Bin 3] INT,[Bin 4] INT,[Bin 5] INT,[Bin 6] INT,[Bin 7] INT,[Bin 8] INT,[Tester ID] VARCHAR(50),[TEST PROGRAM]	VARCHAR(50),
						[Handler ID] VARCHAR(50),[Lot Type] VARCHAR(50),[PACKAGE] VARCHAR(50),[LC] VARCHAR(50),[INSERTION] VARCHAR(50), [LB ID] VARCHAR(50),[Temperature] VARCHAR(50),
						[Date Code] VARCHAR(50),[Loss QTY] INT,[VM QTY] INT, [OS QTY] INT,[Dut Board No] VARCHAR(50) ,[Dut Cable No] VARCHAR(50)  )

	CREATE TABLE #PART_TABLE ( [PART NUMBER] VARCHAR(50), QTY INT , YIELD FLOAT, [QTY_ROW_ID] INT, [YIELD_ROW_ID] INT )

	CREATE TABLE #SUMMARY_TABLE ( [PART NUMBER] VARCHAR(50), [In Qty] INT, [Out Qty] INT, [Yield] FLOAT , [Bin 1] INT,[Bin 2] INT,[Bin 3] INT,[Bin 4] INT,[Bin 5] INT,[Bin 6] INT,[Bin 7] INT,[Bin 8] INT, ORDER_ID INT )
  	
	DECLARE @SQLSTRING	NVARCHAR(4000)
	DECLARE @SQLSTRING1	NVARCHAR(4000)
	DECLARE @EXCELEXECUTESTRING	NVARCHAR(4000)
	DECLARE @CRYSTALEXECUTESTRING	NVARCHAR(4000)
		
	SET @SQLSTRING = N' INSERT INTO #result_table
						SELECT (SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
					CASE 
						WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
						WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
					END 
				),
				P.PART_NUMBER, PS.PROCESSSTEP_ID, LOT_NUMBER,FAB_LOT_NUMBER, ISNULL(TIB.IN_DATE, DATE_IN), ISNULL(TIB.OUT_DATE,Date_Out), ISNULL(IN_QTY, QTY_IN), ISNULL(GOOD_DICE,QTY_OUT),ROUND(Yield, 2),
				(SELECT L_VENDOR_NAME FROM LOCATION WHERE LOCATION_ID = LT.LOCATION_ID ), PS_TYPE, BIN_1,BIN_2,BIN_3,BIN_4,BIN_5,BIN_6,BIN_7,BIN_8,			
				ISNULL(TIB.TESTER_ID,FTL.TESTER_ID), ISNULL(TIB.Test_Pgm,FTL.FTL_Test_Program), ISNULL(TIB.Handler,FTL.Handler), lot_category, ISNULL(TIB.Package,FTL.FTL_Package_Type ), ISNULL(TIB.LC,FTL.FTL_Lead_Count),
				ISNULL(TIB.STAGE,FTL.STAGE), ISNULL(TIB.LB_ID,FTL.LB_ID), ISNULL(TIB.ROOM_TEMP,FTL.ROOM_TEMP), ISNULL(TIB.DATE_CODE,LT.LOT_DATE_CODE), 
				ISNULL(ISNULL(TIB.LOSS_QTY,FTL.LOSS_QTY),0), ISNULL(ISNULL(TIB.VM_QTY,FTL.VM_QTY),0), ISNULL(ISNULL(TIB.OS_QTY,FTL.OS_QTY),0),
				Dut_Board_No, Dut_Cable_No
				FROM PRODUCT P, PROCESS_STEP PS, LOT_TABLE LT, FINAL_TEST_LOT FTL, Test_Informative_Binning TIB
				WHERE P.PROCESSSTEP_ID=PS.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND LT.LOT_ID= FTL.LOT_ID AND FTL.LOT_ID = TIB.LOT_ID AND lot_current_status In ( ''close'', ''HOLD'' ) '
	
	SET @SQLSTRING1 = ''
	
	IF ( @PROCESS_TYPE IS NOT NULL )
	BEGIN
		IF ( @PROCESS_TYPE = '0' or @PROCESS_TYPE = 'all' )
		BEGIN
			SET @PROCESS_TYPE = 'FT'
		END
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND PS.PS_TYPE = ''' +  @PROCESS_TYPE + '''' 
	END

	IF ( @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.PROCESSSTEP_ID = ''' +  @PROCESSSTEP_IDS + '''' 
	END 

	IF ( @BASEPRODUCT_ID IS NOT NULL AND @BASEPRODUCT_ID <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND P_PARENT_PRODUCT_ID =  ''' +  @BASEPRODUCT_ID + '''' 
	END
	
	IF ( @VENDOR IS NOT NULL AND @VENDOR <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE VENDOR_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING (  ''' + @VENDOR + '''' +') ) )'
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND ISNULL(TIB.IN_DATE, DATE_IN) >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00 AM' +''''
	END
	
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND ISNULL(TIB.OUT_DATE,Date_Out) <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59 PM' +''''
	END

	--PRINT @SQLSTRING + @SQLSTRING1

	EXEC (@SQLSTRING + @SQLSTRING1)
	
	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
			SET @EXCELEXECUTESTRING = 'select 
			[PART NUMBER] As [DEVICE],[VENDOR],[LOT NUMBER] As [Subcon Lot No], [FAB LOT NUMBER] as [Wafer Lot No], [FAB LOT NUMBER] As [Ikanons Lot No],[Lot Type], [Package],
			[LC] as [L/C], [INSERTION] , [TEST PROGRAM] As [Program No], [Tester ID], [Handler ID],  
			[LB ID] as [L/B ID], [Dut Board No], [Dut Cable No], [Temperature], [Date Code], [Test In Date], [Test Out Date],[In Qty],[Out Qty], ((([Out Qty]* 100.00)/([In Qty]-[Loss QTY]-[VM QTY]-[OS QTY]) ) /100 ) AS [Yield] ,
			[Bin 1],[Bin 2],[Bin 3],[Bin 4],[Bin 5],[Bin 6],[Bin 7],[Bin 8],
			[Loss QTY], [VM QTY],[OS QTY]
			from #result_table '

			IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
			BEGIN
				SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' WHERE  [Test In Date] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00 AM' +''''
			END
	
			IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
			BEGIN
				SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' AND [Test Out Date] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59 PM' +''''
			END
			SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' order by [FAB LOT NUMBER], [Test In Date],[INSERTION] '

			EXEC (@EXCELEXECUTESTRING)
			
	END
	ELSE IF @REPORT_TYPE = 'SUMMARYREPORT'
	BEGIN	
			INSERT INTO #PART_TABLE SELECT [PART NUMBER] , SUM([In Qty]), AVG(YIELD),
			ROW_NUMBER() over (order by SUM([In Qty]) DESC ) AS RowId, 
			ROW_NUMBER() over (order by AVG([Yield]) DESC ) AS YieldRowId FROM #result_table group by [PART NUMBER] 

			DELETE FROM #PART_TABLE WHERE [QTY_ROW_ID] > 20
			
			SET @EXCELEXECUTESTRING = 'INSERT INTO #SUMMARY_TABLE select [PART NUMBER], SUM([In Qty]),SUM([Out Qty]), AVG([Yield]),SUM([Bin 1]),SUM([Bin 2]),
				SUM([Bin 3]),SUM([Bin 4]),SUM([Bin 5]),SUM([Bin 6]),SUM([Bin 7]),SUM([Bin 8]), 
				(SELECT [YIELD_ROW_ID] FROM #PART_TABLE WHERE [PART NUMBER] = RT.[PART NUMBER] )
			from #result_table RT '

			IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
			BEGIN
				SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' WHERE  [Test In Date] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00 AM' +''''
			END
	
			IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
			BEGIN
				SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' AND [Test Out Date] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59 PM' +''''
			END
			SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' group by [PART NUMBER] '

			EXEC (@EXCELEXECUTESTRING)

			/*INSERT INTO #SUMMARY_TABLE select [PART NUMBER], SUM([In Qty]),SUM([Out Qty]), AVG([Yield]),SUM([Bin 1]),SUM([Bin 2]),
				SUM([Bin 3]),SUM([Bin 4]),SUM([Bin 5]),SUM([Bin 6]),SUM([Bin 7]),SUM([Bin 8]), 
				(SELECT [YIELD_ROW_ID] FROM #PART_TABLE WHERE [PART NUMBER] = RT.[PART NUMBER] )
			from #result_table RT group by [PART NUMBER] */
			
			SELECT [PART NUMBER], [In Qty], [Out Qty], [Yield],[Bin 1],[Bin 2],[Bin 3],[Bin 4],[Bin 5],[Bin 6],[Bin 7],[Bin 8]
				 FROM #SUMMARY_TABLE ORDER BY ISNULL(ORDER_ID, ((SELECT COUNt(*) FROM #result_table) + 1 ) )
			
	END
	ELSE IF @REPORT_TYPE = 'DETAILREPORT'
	BEGIN	
			INSERT INTO #PART_TABLE SELECT [PART NUMBER] , SUM([In Qty]), AVG(YIELD),
			ROW_NUMBER() over (order by SUM([In Qty]) DESC ) AS RowId, 
			ROW_NUMBER() over (order by AVG([Yield]) DESC ) AS YieldRowId FROM #result_table group by [PART NUMBER] 

			DELETE FROM #PART_TABLE WHERE [QTY_ROW_ID] > 20
			SET @EXCELEXECUTESTRING = 'select 
			[PART NUMBER] As [DEVICE],[VENDOR],[LOT NUMBER] As [Subcon Lot No], [FAB LOT NUMBER] as [Wafer Lot No], [FAB LOT NUMBER] As [Ikanons Lot No],[Lot Type], [Package],
			[LC] as [L/C], [INSERTION] , [TEST PROGRAM] As [Program No], [Tester ID], [Handler ID],  
			[LB ID] as [L/B ID], [Dut Board No], [Dut Cable No], [Temperature], [Date Code], [Test In Date], [Test Out Date],[In Qty],[Out Qty], [Yield] ,
			[Bin 1],[Bin 2],[Bin 3],[Bin 4],[Bin 5],[Bin 6],[Bin 7],[Bin 8],
			[Loss QTY], [VM QTY],[OS QTY],
			ISNULL( (SELECT [YIELD_ROW_ID] FROM #PART_TABLE WHERE [PART NUMBER] = RT.[PART NUMBER] ),((SELECT COUNt(*) FROM #result_table) + 1 ) )
			  AS Order_Id,
			ROW_NUMBER() over (order by [Yield] DESC ) AS YieldRowId FROM #result_table RT '

			IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
			BEGIN
				SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' WHERE  [Test In Date] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00 AM' +''''
			END
	
			IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
			BEGIN
				SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' AND [Test Out Date] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59 PM' +''''
			END
			SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' Order by  Order_Id, YieldRowId DESC '

			EXEC (@EXCELEXECUTESTRING)
			
			/*SELECT [PART NUMBER] As [DEVICE],[VENDOR],[LOT NUMBER] As [Subcon Lot No], [FAB LOT NUMBER] as [Wafer Lot No], [FAB LOT NUMBER] As [Customer Lot No],[Lot Type], [Package],
			[LC] as [L/C], [INSERTION] , [TEST PROGRAM] As [Program No], [Tester ID], [Handler ID],  
			[LB ID] as [L/B ID],  [Temperature], [Date Code], [Test In Date], [Test Out Date],[In Qty],[Out Qty], [Yield] ,
			[Bin 1],[Bin 2],[Bin 3],[Bin 4],[Bin 5],[Bin 6],[Bin 7],[Bin 8],
			[Loss QTY], [VM QTY],[OS QTY], 
			ISNULL( (SELECT [YIELD_ROW_ID] FROM #PART_TABLE WHERE [PART NUMBER] = RT.[PART NUMBER] ),((SELECT COUNt(*) FROM #result_table) + 1 ) )
			  AS Order_Id,
			ROW_NUMBER() over (order by [Yield] DESC ) AS YieldRowId FROM #result_table RT 
			Order by  Order_Id, YieldRowId DESC */
	END
	ELSE
	BEGIN
		SET @CRYSTALEXECUTESTRING = ' select * from #result_table '

		IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
		BEGIN
			SET @CRYSTALEXECUTESTRING = @CRYSTALEXECUTESTRING + ' WHERE  [Test In Date] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00 AM' +''''
		END

		IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
		BEGIN
			SET @CRYSTALEXECUTESTRING = @CRYSTALEXECUTESTRING + ' AND [Test Out Date] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59 PM' +''''
		END

		SET @CRYSTALEXECUTESTRING = @CRYSTALEXECUTESTRING + ' order by [FAB LOT NUMBER], [Test In Date],[INSERTION] '
		
		EXEC (@CRYSTALEXECUTESTRING)
	END

END